\set ON_ERROR_STOP

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
    '$libdir/plpgsql' LANGUAGE C;
    
CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
    '$libdir/plpgsql' LANGUAGE C;
    
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
    HANDLER plpgsql_call_handler
    VALIDATOR plpgsql_validator;    

CREATE TABLE ausers (
    userid serial primary key,
    created timestamp DEFAULT now() NOT NULL,
    modified timestamp DEFAULT now() NOT NULL,
    deleted timestamp,
    name varchar(255) DEFAULT 'New User',
    may_modifyusers boolean default false,
    may_modifytasks boolean default false,
    may_logon boolean default false,
    may_viewalltasks boolean default false,
    email varchar(255),
    loginid varchar(255),    
    pwhash varchar(255)
);

grant all on ausers to phptasks;
grant all on ausers_userid_seq to phptasks;

insert into ausers (name, may_modifyusers, may_modifytasks, may_logon, 
                    may_viewalltasks, loginid, pwhash)
    values ('Administrator', true, true, true, true, 'admin', md5('admin'));                    

CREATE TABLE atasks (
    taskid serial primary key,
    created timestamp DEFAULT now() NOT NULL,
    modified timestamp DEFAULT now() NOT NULL,
    deleted timestamp,
    completed timestamp,
    priority smallint DEFAULT 3 NOT NULL,
    title varchar(255) DEFAULT 'New Task' NOT NULL,
    assigned_by integer NOT NULL,
    assigned_to integer NOT NULL,
    description text,
    due timestamp
);

grant all on atasks to phptasks;
grant all on atasks_taskid_seq to phptasks;

CREATE TABLE atasklog (
    tlid serial primary key,
    created timestamp DEFAULT now() NOT NULL,
    modified timestamp DEFAULT now() NOT NULL,
    deleted timestamp,
    taskid integer NOT NULL,
    userid integer NOT NULL,
    entry text NOT NULL
);

grant all on atasklog to phptasks;
grant all on atasklog_tlid_seq to phptasks;

CREATE TABLE priorities (
    num smallint NOT NULL,
    name varchar(20) NOT NULL
);

grant all on priorities to phptasks;

INSERT INTO priorities VALUES (1, 'Critical');
INSERT INTO priorities VALUES (2, 'High');
INSERT INTO priorities VALUES (3, 'Normal');
INSERT INTO priorities VALUES (4, 'Low');
INSERT INTO priorities VALUES (5, 'Very Low');

CREATE VIEW tasklog AS
    SELECT t1.tlid, t1.created, t1.taskid, t1.userid, t1.entry 
    FROM atasklog t1 WHERE (t1.deleted IS NULL);

grant all on tasklog to phptasks;

CREATE VIEW tasklog_list AS
    SELECT t0.tlid, t0.created, t0.taskid, t1.name AS "user", t0.entry 
    FROM tasklog t0, ausers t1 
    WHERE (t1.userid = t0.userid);

grant all on tasklog_list to phptasks;

CREATE VIEW users AS
    SELECT t1.userid, t1.name, t1.may_modifyusers, t1.may_modifytasks, 
           t1.may_logon, t1.may_viewalltasks, t1.loginid, t1.email, 
           t1.pwhash 
    FROM ausers t1 
    WHERE (t1.deleted IS NULL);

grant all on users to phptasks;

CREATE VIEW tasks AS
    SELECT t0.taskid, t0.created, t0.completed, t0.priority, t0.title, 
           t0.assigned_by, t0.assigned_to, t0.description, t0.due 
    FROM atasks t0 
    WHERE (t0.deleted IS NULL);

grant all on tasks to phptasks;

CREATE VIEW task_list AS
    SELECT tasks.taskid, tasks.created, tasks.completed, tasks.priority, 
           priorities.name AS priority_name, by_users.name AS assigned_by, 
           tasks.assigned_by AS assigned_by_id, tasks.assigned_to, 
           to_users.name AS assigned_to_name, tasks.title, tasks.description, 
           tasks.due 
    FROM atasks tasks, ausers by_users, ausers to_users, priorities 
    WHERE tasks.deleted IS NULL
      AND by_users.userid = tasks.assigned_by
      AND to_users.userid = tasks.assigned_to 
      AND priorities.num = tasks.priority;

grant all on task_list to phptasks;
